<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page language="java" import="com.base.myproject.client.jdbc.*" %>
<%@ page language="java" import="com.base.myproject.server.*" %>
<%@ page language="java" import="com.base.myproject.client.jdbc.DataSet" %>
<%@ page language="java" import="com.base.myproject.server.tools.*" %>
<%@ page language="java" import="com.base.myproject.server.tools.*, java.util.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>

<LINK href="/hr_Jsp/report.css" rel=stylesheet>

<title>Insert title here</title>
<%
	String excel = request.getParameter("excel");
	if (excel != null && !excel.equals("")) {
		response.setHeader("Content-disposition","attachment; filename=weekManpower.xls");
	}
	String begintime = request.getParameter("beginTime");
	String endtime = request.getParameter("endTime");
	
	DataSetTool DST = new DataSetTool();;
	DataSet dsDepartment = null; 
	DataSet bigDepartment = null;
	DataSet studentFrom = null;
	String bigDepartmentId = "";
	int col = 0;
	String studentPlus = "";
	
	Map<String, String> departmentName = new HashMap<String, String>();
	Map<String, Integer> departmentSum = new HashMap<String, Integer>();
	
	String studentFromDepartmentList = "部门,sum(预算数) as 预算数 ,sum(实习生数) as 实习生数,sum(现有) as 现有,"+
	  "sum(现有总人数) as 现有总人数,sum(离职) as 离职,sum(入职) as 入职,sum(现有空缺) as 现有空缺";
	String studentFromSmallDepartmentList = "预算数,实习生数,现有,现有+#studentPlus as 现有总人数,离职,入职, 预算数+实习生数-(现有+#studentPlus) as 现有空缺";
	
	String collectSql = "select #smallList  from "+
	"  (select (select sum(persion_num) from job_table) as 预算数,(select isnull(sum(traininger_num),0) from job_table) 实习生数, "+
	"  (select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and convert(varchar(10),entrydate,120) <='"+endtime+
	"' and (convert(varchar(10),lastjobdate,120) > '"+endtime+"' or lastjobdate is null)) as 现有, ";
	String departmentSql = "select #departmentList from( "+
	  "select 部门,#smallList from "+
	  "(select department 部门,isnull(persion_num,0) 预算数,isnull(traininger_num,0) 实习生数, "+
	  "(select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and job_name=bcode_job.employinnertitle and convert(varchar(10),entrydate,120) <='"+endtime+
	  "' and (convert(varchar(10),lastjobdate,120) > '"+endtime+"' or lastjobdate is null) and exists (select 1 from bcode where bcode = bcode_job.bcode and systemid = department)) as 现有, ";
	String smallDepartmentSql = "select 职位名,#smallList from "+
	  "(select job_name 职位名,isnull(persion_num,0) 预算数,isnull(traininger_num,0) 实习生数,"+
	  "(select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and job_name=bcode_job.employinnertitle and convert(varchar(10),entrydate,120) <='"+endtime+
	  "' and (convert(varchar(10),lastjobdate,120) > '"+endtime+"' or lastjobdate is null) and exists (select 1 from bcode where bcode = bcode_job.bcode and systemid = department)) as 现有,";
	
	bigDepartment = DST.getDataSet("select id, name from systeminfo where len(id) = 4 and id != '0011'");
	studentFrom = DST.getDataSet("select name from dictionary where type='HR_StudentFrom'");
	
	col = studentFrom.getRowCount();
	departmentSum.put("预算数", 0);
	departmentSum.put("实习生数", 0);
	departmentSum.put("现有", 0);
	for (int i=0; i<col; i++) {
		String place = studentFrom.getValue(i, "name");
		departmentSum.put(place, 0);
		studentPlus += place + "+";
		studentFromDepartmentList += ",sum(" + place + ") as " + place;
		studentFromSmallDepartmentList += "," + place;
		
		collectSql += "  (select count(*) from bcode_job where convert(varchar(10),entrydate,120) <='"+endtime+
		"' and (convert(varchar(10),lastjobdate,120) > '"+endtime+"' or lastjobdate is null) and jobtype='实习生' and trainerfrom='" + place + "') as " + place + ", ";
		departmentSql += "(select count(*) from bcode_job where job_name=bcode_job.employinnertitle and convert(varchar(10),entrydate,120) <='"+endtime+
		  "' and (convert(varchar(10),lastjobdate,120) > '"+endtime+"' or lastjobdate is null) and jobtype='实习生' and exists (select 1 from bcode where bcode = bcode_job.bcode and systemid = department) and trainerfrom='" + place + "') as " + place + ", ";
		smallDepartmentSql += "(select count(*) from bcode_job where job_name=bcode_job.employinnertitle and convert(varchar(10),entrydate,120) <='"+endtime+
		  "' and (convert(varchar(10),lastjobdate,120) > '"+endtime+"' or lastjobdate is null) and exists (select 1 from bcode where bcode = bcode_job.bcode and systemid = department) and jobtype='实习生' and trainerfrom='" + place + "') as " + place + ",";
	}
	departmentSum.put("现有总人数", 0);
	departmentSum.put("离职", 0);
	departmentSum.put("入职", 0);
	departmentSum.put("现有空缺", 0);
	
	studentPlus = studentPlus.substring(0, studentPlus.length() - 1);
	studentFromSmallDepartmentList = studentFromSmallDepartmentList.replaceAll("#studentPlus", studentPlus);
	collectSql = collectSql.replaceAll("#smallList", studentFromSmallDepartmentList);
	departmentSql = departmentSql.replaceAll("#smallList", studentFromSmallDepartmentList).replaceAll("#departmentList", studentFromDepartmentList);
	smallDepartmentSql = smallDepartmentSql.replaceAll("#smallList", studentFromSmallDepartmentList);
	
	collectSql += "  (select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and convert(varchar(10),lastjobdate,120) between '"+begintime+"' and '"+endtime+"') as 离职, "+
	"  (select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and convert(varchar(10),entrydate,120) between '"+begintime+"' and '"+endtime+"') as 入职  ) as a";
	departmentSql += "(select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and job_name=bcode_job.employinnertitle and exists (select 1 from bcode where bcode = bcode_job.bcode and systemid = department) and convert(varchar(10),lastjobdate,120) between '"+begintime+"' and '"+endtime+"') as 离职, "+
	  "(select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and job_name=bcode_job.employinnertitle and exists (select 1 from bcode where bcode = bcode_job.bcode and systemid = department) and convert(varchar(10),entrydate,120) >= '"+begintime+"' and convert(varchar(10),entrydate,120) <= '"+endtime+"') as 入职 ";
	smallDepartmentSql += "(select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and job_name=bcode_job.employinnertitle and exists (select 1 from bcode where bcode = bcode_job.bcode and systemid = department) and convert(varchar(10),lastjobdate,120) between '"+begintime+"' and '"+endtime+"') as 离职,"+
	  "(select count(*) from bcode_job where jobtype!='实习生' and jobtype!='临时工' and job_name=bcode_job.employinnertitle and exists (select 1 from bcode where bcode = bcode_job.bcode and systemid = department) and convert(varchar(10),entrydate,120) between '"+begintime+"' and '"+endtime+"') as 入职 ";
	for (int i=0; i<bigDepartment.getRowCount(); i++) {
		departmentName.put(bigDepartment.getValue(i, "id"), bigDepartment.getValue(i, "name"));
	}
	if(!"".equals(begintime)){
		dsDepartment = DST.getDataSet("select department,name from " +
				"(select distinct department from job_table where department is not null and department not like '0011%')as a inner join " +
				"(select name,id from systeminfo)as b on a.department=b.id order by id");
	}
%>
</head>
<body>
	<table style="border-collapse: collapse" border="1" align="center"  cellspacing="0" cellpadding="3" border="1" width="100%" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="border-color:#000000;">
		<tr bgcolor="#e8e8e0" align="center">
		  <td rowspan="2">部门</td>
		  <td rowspan="2">预算</td>
		  <td rowspan="2">实习生预算</td>
		  <td rowspan="2" >现有</td>
		  <td colspan="<%= col %>">实习</td>
		  <td rowspan="2" >现有总人数</td>
		  <td rowspan="2" >离职</td>
		  <td rowspan="2">入职</td>
		  <td rowspan="2">现有空缺</td>
	    </tr>
		<tr bgcolor="#e8e8e0" align="center">
			<% for(int i=0; i<col; i++) { %>
		  		<td><%= studentFrom.getValue(i, "name") %></td>
		  	<% } %>
	  	</tr>
	  
	  <%
	  
	  DataSet ds3 = DST.getDataSet(collectSql);
	  for(int i = 0;i < dsDepartment.getRowCount();i++){
		  if (!bigDepartmentId.equals(dsDepartment.getValue(i, "department").substring(0, 4))) {
			  bigDepartmentId = dsDepartment.getValue(i, "department").substring(0, 4);
		  }
		  
		  DataSet ds2 = DST.getDataSet(departmentSql + "from job_table where department='"+dsDepartment.getValue(i,"department")+"') as aaa) as a group by 部门");
		  
		  //统计小部门的总数算为大部门的
			departmentSum.put("预算数", departmentSum.get("预算数") + Integer.parseInt(ds2.getValue(0,"预算数")));
			departmentSum.put("实习生数", departmentSum.get("实习生数") + Integer.parseInt(ds2.getValue(0,"实习生数")));
			departmentSum.put("现有", departmentSum.get("现有") + Integer.parseInt(ds2.getValue(0,"现有")));
			for(int j=0; j<col; j++) {
				String place = studentFrom.getValue(j, "name");
				departmentSum.put(place, departmentSum.get(place) + Integer.parseInt(ds2.getValue(0,place)));
	  		}
			departmentSum.put("现有总人数", departmentSum.get("现有总人数") + Integer.parseInt(ds2.getValue(0,"现有总人数")));
			departmentSum.put("离职", departmentSum.get("离职") + Integer.parseInt(ds2.getValue(0,"离职")));
			departmentSum.put("入职", departmentSum.get("入职") + Integer.parseInt(ds2.getValue(0,"入职")));
			departmentSum.put("现有空缺", departmentSum.get("现有空缺") + Integer.parseInt(ds2.getValue(0,"现有空缺")));
		  
		  DataSet ds = DST.getDataSet(smallDepartmentSql +" from job_table where department='"+dsDepartment.getValue(i,"department")+"') as aaa");
		  %>
			  <tr align="center" bgcolor="#cccc77">
				  <td>【<%=dsDepartment.getValue(i,"name") %>】小计</td>
				  <td><%=ds2.getValue(0,"预算数") %></td>
				  <td><%=ds2.getValue(0,"实习生数") %></td>
				  <td><%=ds2.getValue(0,"现有") %></td>
				  <% for(int k=0; k<col; k++) { %>
		  			<td><%= ds2.getValue(0,studentFrom.getValue(k, "name")) %></td>
		  		  <% } %>
				  <td><%=ds2.getValue(0,"现有总人数") %></td>
				  <td><%=ds2.getValue(0,"离职") %></td>
				  <td><%=ds2.getValue(0,"入职") %></td>
				  <td><%=ds2.getValue(0,"现有空缺") %></td>
			  </tr>
		  <%
		  for(int j = 0;j < ds.getRowCount();j++){
		  %>
		  	<tr align="center">
			  <td><%=ds.getValue(j,"职位名") %></td>
			  <td><%=ds.getValue(j,"预算数") %></td>
			  <td><%=ds.getValue(j,"实习生数") %></td>
			  <td><%=ds.getValue(j,"现有") %></td>
			  <% for(int k=0; k<col; k++) { %>
		  			<td><%= ds.getValue(j,studentFrom.getValue(k, "name")) %></td>
		  	  <% } %>
			  <td><%=ds.getValue(j,"现有总人数") %></td>
			  <td><%=ds.getValue(j,"离职") %></td>
			  <td><%=ds.getValue(j,"入职") %></td>
			  <td><%=ds.getValue(j,"现有空缺")%></td>
		  </tr>
		  <% 
		  }
		  if ((i + 1) == dsDepartment.getRowCount() || !bigDepartmentId.equals(dsDepartment.getValue(i + 1, "department").substring(0, 4))) {
		  %>
			  <tr align="center" bgcolor="#CCFFCC" height="20px">
					  <td>【<%=departmentName.get(bigDepartmentId) %>】总计</td>
					  <td><%=departmentSum.get("预算数") %></td>
					  <td><%=departmentSum.get("实习生数") %></td>
					  <td><%=departmentSum.get("现有") %></td>
					  <% for(int k=0; k<col; k++) { %>
		  				<td><%= departmentSum.get(studentFrom.getValue(k, "name")) %></td>
		  		  	  <% } %>
					  <td><%=departmentSum.get("现有总人数") %></td>
					  <td><%=departmentSum.get("离职") %></td>
					  <td><%=departmentSum.get("入职") %></td>
					  <td><%=departmentSum.get("现有空缺") %></td>
			  </tr>
			  <tr align="center" height="20px">
					  <td colspan="12"></td>
			  </tr>
		  <%
		  	departmentSum.put("预算数", 0);
			departmentSum.put("实习生数", 0);
			departmentSum.put("现有", 0);
			for (int q=0; q<col; q++) {
				String place = studentFrom.getValue(q, "name");
				departmentSum.put(place, 0);
			}
			departmentSum.put("现有总人数", 0);
			departmentSum.put("离职", 0);
			departmentSum.put("入职", 0);
			departmentSum.put("现有空缺", 0);
		  }
	  }
	  %> 
	  <tr align="center">
	  	<td>合计</td>
	  	<td><%=ds3.getValue(0,"预算数") %></td>
		<td><%=ds3.getValue(0,"实习生数") %></td>
		<td><%=ds3.getValue(0,"现有") %></td>
		<% for(int k=0; k<col; k++) { %>
		  	<td><%= ds3.getValue(0,studentFrom.getValue(k, "name")) %></td>
		<% } %>
		<td><%=ds3.getValue(0,"现有总人数") %></td>
		<td><%=ds3.getValue(0,"离职") %></td>
		<td><%=ds3.getValue(0,"入职") %></td>
		<td><%=ds3.getValue(0,"现有空缺") %></td>
	  </tr>
	</table>
</body>
</html>